﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Google.Protobuf.WellKnownTypes;
using MySqlConnector;
using Models.Wishire.entity;
// using MySqlX.XDevAPI;

namespace Models.Wishire.Operator
{
    internal class MysqlOp
    {
        public static string MysqlCnnectionString = "Server =www.yym-free.com; Database =kezhou; Uid =kezhou; Pwd =10708022;Pooling=true;Allow Batch=true";
        //public static string MysqlCnnectionString = "Server =localhost; Database =netschool; Uid =root; Pwd =10708022;Pooling=true;Allow Batch=true";

        MySqlConnection conn = new MySqlConnection(MysqlCnnectionString);
        public MysqlOp()
        {
            return;
        }


        public void Mysqlop(string query)
        {
            //return;
            //数据库连接字符串
            //String connstr = "server=mysql.sqlpub.com:3306;Database =kezhou;uid=wuzhan;pwd=eef3efe3e62990eb;charset=utf8";
            string connstr = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";
            /*string server = "mysql.sqlpub.com";
            int port = 3306;
            string database = "kezhou";
            string username = "wuzhan";
            string password = "eef3efe3e62990eb";
            connstr = $"Server={server};Port={port};Database={database};User ID={username};Password={password};";*/

            //建立数据库连接
            using (conn = new MySqlConnection(connstr))
            {
                conn.Open();
                //启动一个事务
                using (MySqlTransaction transaction = conn.BeginTransaction())
                {

                    using (var cmd = conn.CreateCommand())
                    {
                        try
                        {
                            cmd.Transaction = transaction;
                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                            transaction.Commit();
                            conn.Close();
                        }
                        catch (Exception)
                        {
                            cmd.Transaction = transaction;
                            string currentTime = DateTime.Now.ToString();
                            string Message = "修改信息失败";
                            string add = "insert into log(Timestamp, Message) values('" + currentTime + "', '" + Message + "')";
                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                            transaction.Commit();
                            transaction.Rollback();
                        }
                    }
                }
            }
        }

        public void MysqlDelete(string query)
        {
            string connstr = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";

            using (conn = new MySqlConnection(connstr))
            {
                conn.Open();
                using (MySqlTransaction transaction = conn.BeginTransaction())
                {

                    using (var cmd = conn.CreateCommand())
                    {
                        try
                        {
                            cmd.Transaction = transaction;
                            cmd.CommandText = query;
                            cmd.ExecuteNonQuery();
                            transaction.Commit();
                        }
                        catch (Exception)
                        {
                            transaction.Rollback();
                        }
                    }
                }
            }
        }

        private void Login(string username, string password)
        {
            var InputUserName = username;
            var InputPassWord = password;

            MySqlConnectionStringBuilder builder = new MySqlConnectionStringBuilder();
            builder.UserID = username;
            builder.Password = password;
            builder.Server = "127.0.0.1";
            builder.Port = 3306;
            builder.Database = "cczhuanzhu";
            var MysqlConnector = new MySqlConnection(builder.ConnectionString);
            try
            {

                conn.Open();
                conn.Close();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
        }

        public List<plan_st> Mysql_planst_Query(string query)
        {
            List<plan_st> ans = new List<plan_st>();
            try
            {
                string connectionString = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";

                MySqlConnection connection = new MySqlConnection(connectionString);
                MySqlCommand command = connection.CreateCommand();
                connection.Open();

                command.CommandText = query;

                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    plan_st temp = new plan_st();
                    temp.SetPlanId(Convert.ToInt32(reader["planId"]));
                    temp.SetPlanName(reader["planName"].ToString());
                    temp.SetAccountId(Convert.ToInt32(reader["accountId"]));
                    temp.SetCityName(reader["cityName"].ToString());
                    string time = reader["startTime"].ToString();
                    temp.SetStartTime(reader["startTime"].ToString());
                    temp.SetEndTime(reader["endTime"].ToString());
                    //int w = reader["ifDeleted"];
                    temp.SetIfDeleted(Convert.ToInt32(reader["ifDeleted"]));

                    temp.SetSetTime(DateTime.Parse(reader["setTime"].ToString()));

                    temp.SethotelName(reader["hotelName"].ToString());
                    temp.SethotelLat(Convert.ToDouble(reader["hotelLat"]));
                    temp.SethotelLng(Convert.ToDouble(reader["hotelLng"]));
                    temp.SetEmpty(reader["empty"].ToString());

                    ans.Add(temp);

                }
                reader.Close();
                Console.WriteLine("查询成功");
                return ans;
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
            return ans;
        }

        public List<target_st> Mysql_target_Query(string query)
        {
            List<target_st> ans = new List<target_st>();
            try
            {
                string connectionString = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";

                MySqlConnection connection = new MySqlConnection(connectionString);
                MySqlCommand command = connection.CreateCommand();
                connection.Open();

                command.CommandText = query;

                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    target_st temp = new target_st();
                    temp.SetPlanId(Convert.ToInt32(reader["planId"]));
                    temp.SetTargetName(reader["targetName"].ToString());
                    temp.SetTargetId(Convert.ToInt32(reader["targetId"]));
                    temp.SetTargetCity(reader["targetcity"].ToString());
                    temp.SetTargetAddress(reader["targetAddress"].ToString());
                    temp.SetTargetType(reader["targetType"].ToString());
                    //int w = reader["ifDeleted"];
                    temp.SetMustNight(Convert.ToInt32(reader["mustNight"]));
                    temp.SetTargetLat(Convert.ToDouble(reader["targetLat"]));
                    temp.SetTargetLng(Convert.ToDouble(reader["targetLng"]));
                    temp.SetEmpty(reader["empty"].ToString());

                    ans.Add(temp);

                }
                reader.Close();
                Console.WriteLine("查询成功");
                return ans;
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
            return ans;
        }

        public List<city> Mysql_city_Query(string query)
        {
            List<city> ans = new List<city>();
            try
            {
                string connectionString = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";

                MySqlConnection connection = new MySqlConnection(connectionString);
                MySqlCommand command = connection.CreateCommand();
                connection.Open();

                command.CommandText = query;

                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (ans.Count == 1000) break;
                    city temp = new city();
                    temp.SetType(reader["type"].ToString());
                    temp.SetName(reader["Name"].ToString());
                    temp.SetLat(Convert.ToDouble(reader["Lat"]));
                    temp.SetLng(Convert.ToDouble(reader["Lng"]));
                    temp.SetCityName(reader["city_name"].ToString());
                    ans.Add(temp);

                }
                reader.Close();
                Console.WriteLine("查询成功");
                return ans;
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
            return ans;
        }

        public List<answer> Mysql_answer_Query(string query)
        {
            List<answer> ans = new List<answer>();
            try
            {
                string connectionString = "server=www.yym-free.com;Database =kezhou;uid=kezhou;pwd=10708022;charset=utf8";

                MySqlConnection connection = new MySqlConnection(connectionString);
                MySqlCommand command = connection.CreateCommand();
                connection.Open();

                command.CommandText = query;

                MySqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    if (ans.Count == 1000) break;
                    answer temp = new answer();
                    temp.SetIndex(Convert.ToInt32(reader["answerindex"]));
                    temp.SetIfDeleted(Convert.ToInt32(reader["ifdeleted"]));
                    temp.SetPlanId(Convert.ToInt32(reader["planId"]));
                    temp.SetAnswerId(Convert.ToInt32(reader["answerId"]));
                    temp.SetName(reader["name"].ToString());
                    temp.SetLat(Convert.ToDouble(reader["lat"]));
                    temp.SetLng(Convert.ToDouble(reader["lng"]));
                    ans.Add(temp);

                }
                reader.Close();
                Console.WriteLine("查询成功");
                return ans;
            }
            catch (Exception ex)
            {
                Console.WriteLine("连接失败：" + ex.Message);
            }
            return ans;
        }
    }

}
